1. Technical Field
The present invention is directed to the field of database queries. More specifically, the invention provides a system and method for optimizing the structure and visual display of complex data filters that are used to query a database.
2. Description of the Related Art
There are many known software systems and methods for querying a database. One of the most popular software query languages is known as SQL (or Structured Query Language.) SQL provides a framework for logically structuring complex conditional expressions that can be used to query a database. SQL includes many different types of logical constructs, including the WHERE clause, the HAVING clause and the ON clause. A WHERE clause is typically structured as follows: WHERE (variable 1  less than operator greater than  condition 1) link (variable 2  less than operator greater than  condition 2). The WHERE clause then returns data records from the database that meet the two conditional expressions (variable 1  less than operator greater than  condition 2) link (variable 2  less than operator greater than  condition 2), depending on the type of link. Two common forms of links for conditional expressions are the xe2x80x9cANDxe2x80x9d link and the xe2x80x9cORxe2x80x9d link.
For example, consider a database containing personnel records for a company. Each employees data record may include variable fields for storing salary and age. A user may then query the database to find those employees that are older than 35 and make less than $50,000 by forming the SQL query: WHERE (age greater than 35) AND (salary less than 50,000). Here, age is variable 1, 35 is condition 1, salary is variable 2, and 50,000 is condition 2. The logical link operator is the AND link.
Although it is quite easy to visualize the logic of this simple filter, in many cases a user may desire to utilize a query that includes numerous conditional expressions linked in a variety of complex, nested structures. These types of complex data filters are much more difficult to visualize and understand due to the textual nature of the SQL language.
A system and method for optimizing the structure and display of complex data filters is provided. The system includes software modules for creating, optimizing and then displaying complex data filter queries structured according to a particular query language. The query is modeled by generating a filter network comprising a plurality of filter nodes, wherein each filter node corresponds to a conditional expression in the query. Optimization modules then optimize the structure and visual display of the filter network. The system software modules may include a query input module, a graphical interface module, a visual optimization module, and a query language optimization module. The query input module enables a user to input a data filter query into the system using a particular query language. The graphical interface module then maps the input query into a graphical representation of the data filter using a readily understandable graphical paradigm. The visual optimization module optimizes the graphical display of the query by merging sub-networks of the filter network that generate the same query language, and the query language optimization module optimizes the structure of the input query by eliminating redundant filter nodes.
According to one aspect of the invention, an optimization method for database queries is provided. This method preferably comprises the steps of: (a) receiving a database query described by a query language logical structure; (b) generating a filter network comprising a plurality of filter nodes, wherein the structure of the filter network corresponds to the logical structure of the query; (c) optimizing the structure of the filter network by identifying pairs of redundant filter nodes in the filter network and deleting one of the redundant nodes in the pair; and (d) optimizing the display of the filter network by identifying similar sub-networks in the filter network and merging the sub-networks.
According to another aspect of the invention, a method of displaying a complex data filter is provided. This method preferably comprises the steps of: (i) generating a filter network model for the complex data filter, wherein the filter network model includes an originating node, one or more terminating nodes, and a plurality of filter nodes, wherein the originating node is coupled to the one or more terminating nodes through the plurality of filter nodes; (ii) depicting the originating node as a funnel; (iii) depicting the one or more terminating nodes as faucets; (iv) depicting the filter nodes as boxes, wherein each box contains a conditional expression associated with the filter node; (v) depicting the logical couplings between originating node, the terminating node and the filter nodes using one or more types of pipes; and (vi) displaying the filter network using the funnel, the faucets, the boxes, and the one or more types of pipes.
Yet another aspect of the invention provides a system for optimizing database queries. The system preferably includes: (a) a query input module that receives a database query described by a query language logical structure; (b) a graphical interface module that generates a filter network comprising a plurality of filter nodes, wherein the structure of the filter network corresponds to the logical structure of the query; (c) a structural optimization module that optimizes the structure of the filter network; and (d) a visual optimization module that optimizes the display of the filter network.
Still another aspect of the invention provides a method of structurally optimizing a query, comprising the steps of: (i) generating a filter network comprising a plurality of filter nodes, wherein the structure of the filter network corresponds to the logical structure of the query; (ii) identifying two filter nodes that, together with any succeeding nodes in the filter network, generate the same logical structure; (iii) deleting one of the two filter nodes from the filter network; and (iv) repeating the identifying and deleting steps for each filter node in the filter network.
Another method disclosed provides for visually optimizing a query. This method preferably comprises the steps of: (a) generating a filter network comprising a plurality of filter nodes, wherein the structure of the filter network corresponds to the logical structure of the query; (b) identifying two filter sub-networks that generate the same logical structure; (c) determining whether the two filter networks can be merged; and (d) if so, then merging the two filter sub-networks.
Another method disclosed provides for optimizing the display of complex data filters according to the following steps: (i) receiving a complex data filter including a plurality of filter nodes organized into a filter network, wherein each filter node corresponds to a conditional expression in the complex data filter; (ii) generating a data model for each of the filter nodes in the filter network, wherein the data model includes a conditional expression for each filter node and a corresponding flag that indicates whether or not the node has been optimized; and (iii) optimizing the display of the filter network by: identifying similar sub-networks in the filter network; merging the similar sub-networks; and setting the flag for one of the filter nodes in the merged sub-network to indicate that the node has been optimized.
It should be noted that these are just some of the many aspects of the present invention. Other aspects not specified will become apparent upon reading the detailed description of the preferred embodiment set forth below.